package com.laboratory.report.lab03;

import lombok.SneakyThrows;
import lombok.experimental.UtilityClass;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

/**
 * 数据库
 */
@UtilityClass
public class DbFactory {

    private static final String JDBC_URL = "jdbc:h2:mem:test;LOCK_MODE=1;MODE=MYSQL;";
    private static final String DRIVER_CLASS = "org.h2.Driver";
    private static final String USER = "";
    private static final String PASSWORD = "";
    private static final String CREATE_TABLE = "CREATE TABLE MEDAL(" +
            "ID INT  NOT NULL AUTO_INCREMENT COMMENT '主键'," +
            "QUEUE_NAME VARCHAR(50) NOT NULL COMMENT '代表队'," +
            "GOLD INT  NOT NULL COMMENT '金牌'," +
            "SILVER INT  NOT NULL COMMENT '银牌'," +
            "COPPER INT  NOT NULL COMMENT '铜牌'," +
            "PRIMARY KEY(ID ASC)" +
            ")";
    private static final String INSERT_SQL = "INSERT INTO MEDAL(QUEUE_NAME,GOLD,SILVER,COPPER) VALUES(?,?,?,?)";
    private static final String LOAD_ALL_SQL = "SELECT * FROM MEDAL;";
    private static final String ADD_GOLD_SQL = "update MEDAL set gold=gold+? where queue_name=?";
    private static final String SELECT_BY_QUEUE_NAME = "SELECT * FROM MEDAL WHERE QUEUE_NAME=?";
    private static final String SELECT_BY_MEDAL = "SELECT * FROM MEDAL WHERE GOLD>=? AND SILVER>=? AND COPPER>=?";
    private static final String DELETE_NONE = "DELETE FROM MEDAL WHERE GOLD=0 AND SILVER=0 AND COPPER=0";

    private Connection connection;

    static {
        // 加载数据库驱动
        try {
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    @SneakyThrows
    public Connection getConnection() {
        if (null == connection) {
            connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
            connection.setAutoCommit(true);
        }
        return connection;
    }

    @SneakyThrows
    public void init() {
        Connection connection = getConnection();
        try {
            int create = connection.prepareStatement(CREATE_TABLE).executeUpdate();
            if (0 == create) {
                System.out.println("建表成功");
            } else {
                System.out.println("建表失败");
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        insert("挪威", 16, 8, 13);
        insert("中国", 8, 4, 2);
        insert("美国", 6, 10, 7);
        insert("冰岛", 0, 0, 0);
        insert("荷兰", 8, 5, 4);
        insert("印度", 0, 0, 0);
    }

    @SneakyThrows
    public void insert(String queueName, int gold, int silver, int copper) {
        if(null != getByQueueName(queueName)) {
            return;
        }
        Connection connection = getConnection();
        PreparedStatement pstmt = connection.prepareStatement(INSERT_SQL);
        pstmt.setString(1, queueName);
        pstmt.setInt(2, gold);
        pstmt.setInt(3, silver);
        pstmt.setInt(4, copper);
        pstmt.executeUpdate();
    }

    @SneakyThrows
    public Vector<Vector<Object>> loadAllData() {
        Vector<Vector<Object>> rowData = new Vector<>();
        Connection connection = getConnection();
        ResultSet rs = connection.createStatement().executeQuery(LOAD_ALL_SQL);
        while (rs.next()) {
            Vector<Object> data = getRsData(rs);
            rowData.add(data);
        }
        rs.close();
        return rowData;
    }

    @SneakyThrows
    public void addGold(String queueName, int goldAdd) {
        Connection connection = getConnection();
        PreparedStatement pstmt = connection.prepareStatement(ADD_GOLD_SQL);
        pstmt.setInt(1, goldAdd);
        pstmt.setString(2, queueName);
        pstmt.executeUpdate();
    }

    @SneakyThrows
    public Vector<Object> getByQueueName(String queueName) {
        Connection connection = getConnection();
        PreparedStatement pstmt = connection.prepareStatement(SELECT_BY_QUEUE_NAME);
        pstmt.setString(1, queueName);
        ResultSet rs = pstmt.executeQuery();
        Vector<Object> rowData = null;
        if(rs.next()) {
            rowData = getRsData(rs);
        }
        rs.close();
        return rowData;
    }

    @SneakyThrows
    public Vector<Vector<Object>> getByMedal(int gold, int silver, int copper) {
        Connection connection = getConnection();
        PreparedStatement pstmt = connection.prepareStatement(SELECT_BY_MEDAL);
        pstmt.setInt(1, gold);
        pstmt.setInt(2, silver);
        pstmt.setInt(3, copper);
        ResultSet rs = pstmt.executeQuery();
        Vector<Vector<Object>> rowData = new Vector<>();
        while(rs.next()) {
            rowData.add(getRsData(rs));
        }
        rs.close();
        return rowData;
    }

    @SneakyThrows
    public void deleteNone() {
        getConnection().prepareStatement(DELETE_NONE).executeUpdate();
    }

    @SneakyThrows
    private Vector<Object> getRsData(ResultSet rs) {
        Vector<Object> data = new Vector<>();
        int id = rs.getInt("id");
        String queueName = rs.getString("queue_name");
        int gold = rs.getInt("gold");
        int silver = rs.getInt("silver");
        int copper = rs.getInt("copper");
        int total = gold + silver + copper;
        data.add(id);
        data.add(queueName);
        data.add(gold);
        data.add(silver);
        data.add(copper);
        data.add(total);
        return data;
    }
}
